﻿CREATE PROCEDURE [dbo].[proc_Order_Getlist_Finish]
	(
		@CompanyId int,
		@DepId int,
		@StateId int,
		@TypeId int,
		@WayId int,
		@ProcessId int,
		@Stext nvarchar(50),
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@StartIndex int,
		@EndIndex int
	)
AS
begin
	/*Departments*/
--	declare @indextable table(id int identity(1,1),nid int)
--	;WITH ChildDepartment(ID,DName,PId) AS
--	(
--		SELECT ID,DName,ParentID FROM department WHERE id=@DepId
--	    
--		UNION ALL
--		SELECT department.ID,department.DName,department.ParentID FROM ChildDepartment AS d
--		INNER JOIN department ON d.ID = department.ParentID
--	)
--	
--	insert into @indextable(nid) select Id from ChildDepartment;

	Declare @bmbh_T  varchar(30)
	Select @bmbh_T=bmbh From department Where id=@DepId

	;WITH list As(Select ROW_NUMBER() OVER (ORDER BY Id DESC)AS Row
		,Id
		,OId
		,OrderId
		,CusId
		,CusName
		,CusPerson
		,CusPersonTel
		,CusDescription
		,CusEstimate
		,TypeId
		,TypeName
		,WayId
		,WayName
		,ProcessId
		,ProcessName
		,Salesman
		,SalesmanAccount
		,TradeDate
		,CreateDate
		,YingShouPrice
		,ShiShouPrice
		,Discount
		,PaymentTypeId
		,PaymentType
		,BankId
		,Bank
		,PaymentDescription
		,OtherDescription
		,DepId
		,CompanyId
		,StateId
		,IsGreen
		,IsNULL((Select Title From Order_State Where Id=[Order].StateId),'---') As StateName
		From [Order]
		Where (@CompanyId=0 Or CompanyId=@CompanyId) and (@DepId=0 Or DepId in(Select Id From department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T))
		and (@StateId=0 Or StateId=@StateId) and (@TypeId=0 Or TypeId=@TypeId) and (@WayId=0 Or WayId=@WayId) and (@ProcessId=0 Or ProcessId=@ProcessId)
		and (Convert(nchar(10),TradeDate,120) between @StartDate and @EndDate)
		and (@Stext='' Or CusName like '%'+@Stext+'%' Or Salesman like '%'+@Stext+'%')
		and IsGreen=0
	)
	
	Select * From list Where Row Between @StartIndex and @EndIndex
end
	RETURN
